Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ALTER FUNCTION

Purpose

Use the `ALTER` `FUNCTION` statement to recompile an invalid standalone stored function. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.

The `ALTER` `FUNCTION` statement is similar to ALTER PROCEDURE. For information on how Oracle Database recompiles functions and procedures, see Oracle Database Concepts.

This statement does not change the declaration or definition of an existing function. To redeclare or redefine a function, use the `CREATE` `FUNCTION` statement with the `OR` `REPLACE` clause. See CREATE FUNCTION.

Prerequisites

The function must be in your own schema or you must have `ALTER` `ANY` `PROCEDURE` system privilege.

Syntax

alter_function::=

Description of the illustration alter_function.gif

compiler_parameters_clause::=

Description of the illustration compiler_parameters_clause.gif

Semantics

schema

Specify the schema containing the function. If you omit `schema`, Oracle Database assumes the function is in your own schema.

function

Specify the name of the function to be recompiled.

COMPILE

Specify `COMPILE` to cause Oracle Database to recompile the function. The `COMPILE` keyword is required. If Oracle Database does not compile the function successfully, you can see the associated compiler error messages with the SQL*Plus command `SHOW` `ERRORS`.

During recompilation, Oracle Database drops all persistent compiler switch settings, retrieves them again from the session, and stores them at the end of compilation. To avoid this process, specify the `REUSE` `SETTINGS` clause.

DEBUG

Specify `DEBUG` to instruct the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger. Specifying this clause has the same effect as specifying `PLSQL_DEBUG` = `TRUE` in the `compiler_parameters_clause`.

compiler_parameters_clause

Use this clause to specify a value for one of the PL/SQL persistent compiler parameters. The value of these initialization parameters at the time of compilation is stored with the unit's metadata. You can learn the value of such a parameter by querying the appropriate `*_PLSQL_OBJECT_SETTINGS` view. The PL/SQL persistent parameters are `PLSQL_OPTIMIZE_LEVEL`, `PLSQL_CODE_TYPE`, `PLSQL_DEBUG`, `PLSQL_WARNINGS`, `PLSQL_CCFLAGS`, and `NLS_LENGTH_SEMANTICS`.

You can specify each parameter only once in each statement. Each setting is valid only for the current library unit being compiled and does not affect other compilations in this session or system. To affect the entire session or system, you must set a value for the parameter using the `ALTER` `SESSION` or `ALTER` `SYSTEM` statement.

If you omit any parameter from this clause and you specify `REUSE SETTINGS`, then if a value was specified for the parameter in an earlier compilation of this library unit, Oracle Database uses that earlier value. If you omit any parameter and either you do not specify `REUSE SETTINGS` or no value has been specified for the parameter in an earlier compilation, then the database obtains the value for that parameter from the session environment.

Restriction on the compiler_parameters_clause You cannot set a value for the `PLSQL_DEBUG` parameter if you also specify `DEBUG`, because both clauses set the `PLSQL_DEBUG` parameter, and you can specify a value for each parameter only once.

See Also: Oracle Database Reference for the valid values and semantics of each of these parameters and PL/SQL User's Guide and Reference for information on using the `PLSQL_CCFLAGS` parameter for PL/SQL conditional compilation

REUSE SETTINGS

Specify `REUSE` `SETTINGS` to prevent Oracle from dropping and reacquiring compiler switch settings. With this clause, Oracle preserves the existing settings and uses them for the recompilation of any parameters for which values are not specified elsewhere in this statement.

For backward compatibility, Oracle Database sets the persistently stored value of the `PLSQL_COMPILER_FLAGS` initialization parameter to reflect the values of the `PLSQL_CODE_TYPE` and `PLSQL_DEBUG` parameters that result from this statement.

See Also: Oracle Database Reference for the valid values and semantics of each of these parameters and PL/SQL User's Guide and Reference and Oracle Database Application Developer's Guide - Fundamentals for more information on the interaction of the `PLSQL_COMPILER_FLAGS` initialization parameter with the `COMPILE` clause

Example

Recompiling a Function: Example To explicitly recompile the function `get_bal` owned by the sample user `oe`, issue the following statement:

    ALTER FUNCTION oe.get_bal
       COMPILE;

If Oracle Database encounters no compilation errors while recompiling `get_bal`, then `get_bal` becomes valid. Oracle Database can subsequently execute it without recompiling it at run time. If recompiling `get_bal` results in compilation errors, then Oracle Database returns an error, and `get_bal` remains invalid.

Oracle Database also invalidates all objects that depend upon `get_bal`. If you subsequently reference one of these objects without explicitly recompiling it first, then Oracle Database recompiles it implicitly at run time.